




package com.letoken.platform.repo.ecommerce.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.letoken.platform.repo.ecommerce.dto.ShopOrderReq;
import com.letoken.platform.repo.ecommerce.dto.UserOrderReq;
import com.letoken.platform.repo.ecommerce.po.EcommerceOrderDetail;
import com.letoken.platform.repo.ecommerce.po.EcommerceUserMasterOrder;
import com.letoken.platform.repo.ecommerce.po.EcommerceUserOrder;
import com.letoken.platform.repo.ecommerce.vo.*;
import com.letoken.platform.repo.mall.req.MergeData;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 订单 Mapper 接口
 * </p>
 *
 * @author author
 * @since 2023-09-08
 */
public interface EcommerceUserOrderMapper extends BaseMapper<EcommerceUserOrder> {
    @Select(" select t1.user_id, t1.id, t3.delivery_no, t3.receiver_phone " +
            "from ecommerce_user_master_order t1 " +
            "         inner join ecommerce_user_order t2 on t1.father_no = t2.father_no " +
            "         inner join ecommerce_order_detail t3 on t2.id = t3.id " +
            "where t2.status = 2 " +
            "  and to_days(now()) - to_days(t3.delivery_time) > 12 " +
            "  and t1.father_no not in (select euo.father_no " +
            "                    from ecommerce_user_order euo " +
            "                             inner join ecommerce_after_order eao  on euo.id = eao.order_id " +
            "                    where eao.status = 0)")
    List<UserOrderConfirmVo> autoConfirmReceiptOrders(@Param("days") Integer days);


    // 如果结果 =0表示全部评价   >0 表示部分评价
    @Select("select count(1)   -  sum(reviewed)  from ecommerce_user_order where father_no = #{fatherNo} and status = 3 ")
    int isPart(@Param("fatherNo") String fatherNo);


    @Select("select refund_quantity from ecommerce_after_order where id = #{afterId} limit 1")
    Integer selectSkuIdByAfterOrderId(@Param("afterId") Integer afterId);

    @Select("select sku_id from ecommerce_user_order where id = #{orderId} limit 1")
    Integer selectSkuIdByOrderId(@Param("orderId") Integer orderId);

    @Select("select quantity from ecommerce_user_order where id = #{orderId}")
    Integer selectOrderQuantity(@Param("orderId") Integer orderId);


    @Select("SELECT status ,SUM(1) as quantity  from ecommerce_user_master_order  where mall_id  = #{mallId} group by status")
    List<EcommerceUserOrder> selectByMall(@Param("mallId") Integer mallId);

    @Select("select id,create_time,user_id from ecommerce_user_master_order where status = 0 order by create_time asc ")
    List<EcommerceUserMasterOrder> waitPayOrders();


    @Select("select * from ecommerce_user_order where id = #{id} and user_id = #{userId} ")
    EcommerceUserOrder selectOrderByIdAndUserId(@Param("id") Integer id, @Param("userId") Integer userId);


    @Select("select o.shipping,o.father_no, o.status,o.pay_amount, o.id,o.total_amount, s.user_id as toUserId from ecommerce_user_master_order o  inner join app_mall_info s on o.mall_id = s.id  where o.id = #{id} and o.user_id = #{userId}")
    UserOrderPayVo payInfoByOrderId(@Param("userId") Integer userId,@Param("id") Integer id);


    Page<ShopOrderRes> msgOrderList(@Param("page") Page<ShopOrderRes> page , @Param("req") ShopOrderReq req);

    List<ShopOrderSubRes> msgOrderSubList(@Param("fatherNo") String fatherNo);



    //    @Select("select  uo.rmb_rate,uo.contributing, uo.user_id,uo.pay_amount, uo.id,uo.order_no,uo.product_title,ps.real_price ,uo.shipping ,uo.create_time ,uo.update_time ,p.pic as productPic,ps.sp_data,uo.quantity,od.delivery_time,od.remark,uo.total_amount,od.payment_time,uo.status,uo.user_id,od.receiver_name,od.receiver_phone,od.receiver_province,od.receiver_city,od.receiver_region,od.receiver_address,uo.price,od.delivery_company,od.delivery_no from ecommerce_user_order uo inner join ecommerce_order_detail od on uo.id = od.id inner join ecommerce_product p on uo.product_id = p.id inner join ecommerce_product_detail pd on uo.product_id=pd.id inner join ecommerce_product_sku ps on uo.sku_id = ps.id  where uo.id = #{id}")
//    ShopOrderDetailRes managerOrderDetail(@Param("id") Integer id);
    @Select("select  *  from ecommerce_user_master_order where  id = #{id}")
    ShopOrderDetailRes managerOrderDetail(@Param("id") Integer id);



    @Select("select * from ecommerce_user_order where id = #{id} for update")
    EcommerceUserOrder selectForUpdate(@Param("id") Integer id);




    @Select("select * from ecommerce_user_order where father_no = #{fatherNo} ")
    List<EcommerceUserOrder> selectByFatherNo(@Param("fatherNo") String fatherNo);

    @Select("select * from ecommerce_user_order where father_no = #{fatherNo} and status = 2")
    List<EcommerceUserOrder> selectConfirmByFatherNo(@Param("fatherNo") String fatherNo);





    @Select("select o.id,o.product_title,p.pic as productPic,o.real_price,o.product_attr as spData,o.quantity,o.total_amount,o.total_amount_cap,o.order_no,o.contributing,(o.real_price * o.rmb_rate ) as rmbPrice,o.rmb_rate  from ecommerce_user_order o inner join ecommerce_product p on o.product_id = p.id  where  o.father_no = #{fatherNo} ")
    List<ShopOrderNewDetailSubRes> msgSelectByFatherNo(@Param("fatherNo") String fatherNo);

    @Select("select d.* from ecommerce_user_order o inner join ecommerce_order_detail d on o.id = d.id  where o.father_no = #{fatherNo} ")
    List<EcommerceOrderDetail> selectDetailByFatherNo(@Param("fatherNo") String fatherNo);

    @Select("select d.*,o.status from ecommerce_user_order o inner join ecommerce_order_detail d on o.id = d.id  where o.father_no = #{fatherNo} ")
    List<EcommerceUserMasterOrder> selectMergeByFatherNo(@Param("fatherNo") String fatherNo);


    @Select("select 1 from ecommerce_user_order o  where o.father_no = #{fatherNo} and o.status !=3 and o.status !=4 limit 1")
    Integer existsWait(@Param("fatherNo") String fatherNo);


    @Select("select 1 from ecommerce_user_order o  where o.father_no = #{fatherNo} and o.status =3  limit 1")
    Integer existsFinish(@Param("fatherNo") String fatherNo);


    Page<UserOrderRes> selectOrder(@Param("page") Page<UserOrderRes> page , @Param("req") UserOrderReq req);

    List<UserOrderNewDetailSubRes> selectSubOrder(@Param("req")  UserOrderReq req );

    @Select("SELECT status , count(1) as quantity  FROM ecommerce_user_master_order  where user_id = #{userId}  group by status ")
    List<EcommerceUserOrder> statusGroup(@Param("userId") Integer userId);





    @Select("SELECT count(1)   FROM ecommerce_user_order  where user_id = #{userId} and status = 3 and reviewed = 0 ")
    Integer waitEvaluate(@Param("userId") Integer userId);



    @Select("select count(1) from ecommerce_user_order where mall_id = #{shopId} and  (status = 1 or status = 2) ")
    Integer selectWaitDroOrderCount(@Param("shopId") Integer shopId);


    @Select("select count(1) from ecommerce_after_order a inner join ecommerce_user_order o on o.id = a.order_id   where o.mall_id = #{mallId}  and a.status != 3 ")
    Integer selectWaitDroAfterOrderCount(@Param("mallId") Integer mallId);



    @Select("select o.contributing ,sum(o.pay_amount - o.shipping - o.refund_amount) as amount from ecommerce_user_order o inner join app_user_assets_history h on o.order_no = h.sn  where h.create_time >= #{startTime} and h.create_time < #{endTime}   and h.type = 36  and (o.pay_amount - o.shipping - o.refund_amount) > 0 group by o.contributing ")
    List<MergeData> dayBonus(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);



    @Select("select 1 from app_mall_ambassador_record where create_time >= #{startTime} and create_time < #{endTime}  and mall_type = 2 limit 1 ")
    Integer existsBonus(@Param("startTime")LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);





    void updateBatchStateById(@Param("list") List<EcommerceUserOrder> list);




    Page<OrderRes> orders(@Param("page") Page<UserOrderRes> page , @Param("req") UserOrderReq req);

    List<OrderProductRes> orderProducts( String fatherNo);


    @Select("select ifnull(sum((pay_amount - refund_amount) * (1 - contributing)),0) from ecommerce_user_order where status =  3")
    BigDecimal incomeStatistics();


    @Select("select ifnull(sum((pay_amount - refund_amount) * (1 - contributing)),0) from ecommerce_user_order where status =  3 and date(update_time) = date(CURRENT_TIMESTAMP)")
    BigDecimal todayIncomeStatistics();


    @Select("select ifnull(sum(pay_amount),0) from ecommerce_user_order where status =  3")
    BigDecimal orderIncomeStatistics();


    @Select("select ifnull(sum(pay_amount),0) from ecommerce_user_order where status =  3 and date(update_time) = date(CURRENT_TIMESTAMP)")
    BigDecimal todayOrderIncomeStatistics();


    @Select("select count(t1.father_no) from (select distinct father_no from ecommerce_user_master_order where status = 3) t1")
    BigDecimal countQuantity();



    @Select("select count(t1.father_no) from (select distinct father_no from ecommerce_user_master_order where status = 3 and date(update_time) = date(CURRENT_TIMESTAMP)) t1")
    BigDecimal countTodayQuantity();



}

